package com.order.model;  

import java.io.File;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.ext.render.excel.PoiRender;
import com.jfinal.kit.PathKit;
import com.jfinal.kit.StringKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.order.shiro.ShiroUtils;
import com.order.utils.DateUtils;

/** 
 * @Title: Dish.java 
 * @Package com.order.model 
 * @Description: 菜品类
 * @author LinBo
 * @date 2014-3-25 下午11:23:10 
 */

@TableBind(tableName="dish", pkName="id")
public class Dish extends Model<Dish> {

	/** 
	 * @Fields serialVersionUID : 当前类可序列化的版本
	 */  
	private static final long serialVersionUID = 3382773681391055969L;
	public static final Dish dao = new Dish();
	public static final String ID = "id";
	public static final String NAME = "name";
	public static final String INFO = "info";
	public static final String PRICE = "price";
	public static final String IMAGE = "image";
	public static final String STATUS = "status";
	public static final String VIEWCOUNT = "viewcount";
	public static final String LAST_UPDATED_BY = "last_updated_by";
	public static final String LAST_UPDATE_DATE = "last_udpate_date";
	
	
	public Map<String, Object> getDishJson(int page, int pagesize,JSONObject jsonObj) {
		String select = "select d.id, d.name, d.info, d.status, d.last_update_date, d.last_updated_by ";
		String sqlExceptSelect = "from dish d,cuisine c, cuisine_dish cd "
				+ " where cd.dish_id = d.id "
				+ " and cd.cuisine_id = c.id ";

		// 菜系名
		if (StringKit.notBlank(jsonObj.getString("NAME"))) {
			String name = jsonObj.getString("NAME");
			sqlExceptSelect += "  and c.name like '%" + name + "%'";
		}
		// 状态
		if (StringKit.notBlank(jsonObj.getString("STATUS"))) {
			String status = jsonObj.getString("STATUS");
			sqlExceptSelect += "  and c.status like '%" + status + "%'";
		}
		// 修改时间
		if (StringKit.notBlank(jsonObj.getString("LAST_UPDATE_DATE"))) {
			String lastUpdateDate = jsonObj.getString("LAST_UPDATE_DATE");
			sqlExceptSelect += "  and c.last_update_date like '%" + lastUpdateDate.substring(0, 10) + "%'";
		}
		
		Page<Dish> list = dao.paginate(page, pagesize, select,sqlExceptSelect);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("Rows", list.getList());
		map.put("Total", list.getTotalRow());
		return map;
	}
	
	/** 
	* @描述: 获取子节点，用于加载系统菜单
	*/ 
	public String getObjByCuisineId(int cuisineId) {
		String sql = "select d.id dish_id, d.name dish_name "
				+ " from dish d where d.id = ? order by d.sq";
		List<Dish> children_json = dao.find(sql, cuisineId);
		StringBuffer j_s = new StringBuffer();
		if (children_json != null && children_json.size() > 0) {
			j_s.append(",\"children\":[");
			for (int i = 0; i < children_json.size(); i++) {
				j_s.append("{\"dish_id\":\""	+ children_json.get(i).getNumber("dish_id").intValue() + "\",");
				j_s.append("\"text\":\""+ children_json.get(i).getStr("dish_name").toString().trim()+ "\",");
				j_s.append("\"cuisine_id\":\"" + cuisineId + "\",");
				j_s.append("},");
			}
		} else {
			j_s.append("},");
		}
		if (j_s.toString().contains("children")) {
			j_s = new StringBuffer(j_s.substring(0, j_s.lastIndexOf(",")));
			j_s.append("]},");
		}
		return j_s.toString();
	}

	/** 
	* @Title: getDishInfo 
	* @描述: 获取菜品详情
	* @param cuisineDishId
	* @return   
	*/ 
	public Dish getDishInfo(String cuisineDishId) {
		String sql = " select d.id dish_id, cd.id cuisine_dish_id, d.name, d.info, d.price, d.image,"
				+ " d.status, d.viewcount, avg(ol.grade) grade "
			+ " from dish d, order_line ol,  cuisine_dish cd "
			+ " where cd.id = ol.cuisine_dish_id "
			+ " and cd.dish_id = d.id "
			+ " and d.status = '1' "
			+ " and ol.status = '4' "
			+ " and cd.id = ? ";
		return dao.findFirst(sql, cuisineDishId);
	}

	/** 
	* @Title: mySave 
	* @Description: 保存
	* @param json
	* @return   
	*/ 
	public String mySave(final JSONObject json) {
		String msg = "保存成功";
		try{
			Db.tx(new IAtom(){
				@Override
				public boolean run() throws SQLException {
					String tempPath = json.getString("IMAGE");
					String cuisineId = json.getString("CUISINE_ID");
					
					String name = tempPath.substring(tempPath.lastIndexOf("/") + 1);
					String webRoot = PathKit.getWebRootPath().replace("\\", "/");
					String savePath = tempPath.substring(0, tempPath.lastIndexOf(name))
							.replace("temp", "cuisine/" + cuisineId);
					String newPath = webRoot + savePath;
					File path = new File(newPath);
					path.mkdirs();
					File newFile = new File(path, name);
					File oldFile = new File(webRoot + tempPath);
					oldFile.renameTo(newFile);
					oldFile.deleteOnExit();
					
					String insertDish = " insert into dish "
							+ " (name, sq, info, price, image, status, last_updated_by, last_update_date) "
							+ " values ("
							+ "'" + json.get("NAME") + "',"
							+ "'" + json.get("SQ") + "',"
							+ "'" + json.get("INFO") + "',"
							+ "'" + json.get("PRICE") + "',"
							+ "'" + savePath + name + "',"
							+ "'" + json.get("STATUS") + "',"
							+ "'" + ShiroUtils.getUserName() + "',"
							+ "'" + DateUtils.getNowTime() + "')";
					System.out.println("sql:"+insertDish);
					Db.update(insertDish);
					String id = Db.queryNumber("select max(d.id) id from dish d ").toString();
					String insertCD = " insert into cuisine_dish  "
							+ " (cuisine_id, dish_id, last_updated_by, last_update_date) "
							+ " values("
							+ "'" + json.get("CUISINE_ID") + "',"
							+ id + ","
							+ "'" + ShiroUtils.getUserName() + "',"
							+ "'" + DateUtils.getNowTime() + "')";
					System.out.println("sql:"+insertCD);
					Db.update(insertCD);
					return true;
				}});
		}catch(Exception e){
			e.printStackTrace();
			msg = "保存失败";
		}
		return msg;
	}

	/** 
	* @Title: myUpdate 
	* @Description: 更新
	* @param json
	* @return   
	*/ 
	public String myUpdate(JSONObject json) {
		String msg = "";
		String cuisineId = CuisineDish.dao.findFirst("select cd.cuisine_id from cuisine_dish cd where cd.dish_id =?", json.get("DISH_ID")).getNumber("CUISINE_ID").toString();
		String oldPath = json.getString("IMAGE");
		
		String name = oldPath.substring(oldPath.lastIndexOf("/") + 1);
		String webRoot = PathKit.getWebRootPath().replace("\\", "/");
		String savePath = oldPath.substring(0, oldPath.lastIndexOf(name))
				.replace("temp", "cuisine/" + cuisineId);
		String newPath = webRoot + savePath;
		
		File path = new File(newPath);
		path.mkdirs();
		File newFile = new File(path, name);
		File oldFile = new File(webRoot + oldPath);
		oldFile.renameTo(newFile);
		oldFile.deleteOnExit();
		
		String sql = " update dish d set "
				+ " d.name = '" + json.get("NAME") + "', "
				+ " d.sq = '" + json.get("SQ") + "', "
				+ " d.info = '" + json.get("INFO") + "', "
				+ " d.price = '" + json.get("PRICE") + "', ";
		if(StringKit.notBlank(json.getString("IMAGE"))){
			sql += " d.image = '" + savePath + name + "', ";
		}
		sql +=  " d.status = '" + json.get("STATUS") + "' "
				+ " where d.id = '" + json.get("DISH_ID") + "'";
		try{
			Db.update(sql);
			msg = "更新成功";
		}catch(Exception e){
			e.printStackTrace();
			msg = "更新失败";
		}
		return msg;
	}

	/** 
	* @Title: myDelete 
	* @Description: 菜品删除
	* @param json
	* @return   
	*/ 
	public String myDelete(JSONObject json) {
		String msg = "删除成功";
		try{
			String sql = " select * from cuisine_dish cd where cuisine_id ="
					+ json.get("CUISINE_ID") + " and dish_id = " + json.get("DISH_ID");
			CuisineDish.dao.findFirst(sql).delete();
			Dish dish =  dao.findById(json.get("DISH_ID"));
			File image = new File(dish.getStr("image"));
			image.deleteOnExit();
			dish.delete();
		}catch(Exception e){
			e.printStackTrace();
			msg = "删除失败";
		}
		return msg;
	}
	
	public Map<String, Object> getDishMap(int page, int pagesize,JSONObject jsonObj) {
		String select = " select cdc.*, ifnull(sum(ol.quantity), 0) count, c.name cuisine_name ";
		String sqlExceptSelect = "from order_line ol right join ( "
							+ " select d.*,d.id d_id,cd.id cd_id, cd.cuisine_id c_id "
							+ " from dish d, cuisine_dish cd "
							+ " where d.id = cd.dish_id) cdc "
							+ " on ol.cuisine_dish_id=cdc.cd_id, cuisine c "
							+ " where c.id = cdc.c_id "
							+ " group by cdc.d_id ";

		Page<Dish> list = dao.paginate(page, pagesize, select,sqlExceptSelect);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("Rows", list.getList());
		map.put("Total", list.getTotalRow());
		return map;
	}
	
	public Map<String, Object> getExcelMap(int page, int pagesize){
		String select = "select d.name, d.info, d.price, d.viewcount, d.status, c.name cuisine_name, ifnull(sum(ol.quantity), 0) count ";
		String sqlExceptSelect  = " from dish d, cuisine_dish cd, cuisine c, order_line ol"
				+ " where cd.dish_id = d.id"
				+ " and cd.cuisine_id = c.id "
				+ " and cd.id = ol.cuisine_dish_id "
				+ " group by d.id ";
		Page<Record> list = Db.paginate(page, pagesize, select, sqlExceptSelect);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("Rows", list.getList());
		map.put("Total", list.getTotalRow());
		return map;
	}
	/** 
	* @Title: getExcel 
	* @描述: 获取excel
	* @param page
	* @param pagesize
	* @param jsonObject
	* @return   
	*/ 
	public PoiRender getExcel(int page,int pagesize){
		String[]  headerStrings = new String[]{
				"菜系", 
				"菜名", 
				"简介",
				"价格", 
				"查看次数",
				"状态",
				"累计销售量"
		};
		String[] columnStrings = new String[]{
				"CUISINE_NAME", 
				"NAME",
				"INFO",
				"PRICE",
				"VIEWCOUNT", 
				"STATUS",
				"COUNT"
		};
		Map<String, Object> map = getExcelMap(page, pagesize);
		//把null替换成""，防止导出excel的时候显示null
				List<Map> mapList = (List<Map>) map.get("Rows");
				if(mapList.size()>0){
					for(int i =0;i<mapList.size();i++){
						Record record = (Record) mapList.get(i);
						Map<String, Object> map2 = record.getColumns();
						Iterator<String> iterator = map2.keySet().iterator();
						while (iterator.hasNext()) {    
					         String key = (String)iterator.next();    
					         if(map2.get(key)==null){
					        	 map2.put(key, "");
					         }
					         if("STATUS".equals(key)){
					        	 int status = Integer.valueOf(map2.get(key).toString());
					        	 switch(status){
					        	 case 0 : map2.put(key, "禁用");	break;
					        	 case 1 : map2.put(key, "启用");	break;
					        	 case 2 : map2.put(key, "缺货");	break;
					        	 default : break;
					        	 }
					         }
						} 
						
					}
				}
				PoiRender poiRender = PoiRender.me((List<?>)map.get("Rows"));
				poiRender.fileName("DishReport.xls");
				poiRender.cellWidth(5000);
				poiRender.headers(headerStrings);
				poiRender.columns(columnStrings);
				poiRender.sheetName("菜品报表");
				return poiRender;
	}

	/** 
	* @Title: isOnSell 
	* @描述: 判断菜品是否在售状态
	* @param cuisineDishId
	* @return   
	*/ 
	public boolean isOnSell(String cuisineDishId) {
		CuisineDish cd = CuisineDish.dao.findById(cuisineDishId);
		if(cd != null){
			Dish d = dao.findById(cd.get("dish_id"));
			if(d != null && "1".equals(d.get("status"))){
				return true;
			}
		}
		return false;
	}



	/** 
	* @Title: getSum 
	* @描述: 合计购物车中的总价格
	* @param cart
	* @return   
	*/ 
	public double getSum(Map<String, Integer> cart) {
		double total = 0;
		if(cart != null && !cart.isEmpty()){
			Iterator<String> it = cart.keySet().iterator();
			while(it.hasNext()){
				int cuisineDishId = Integer.valueOf(it.next().toString());
				String sql = " select d.id dish_id, d.name,d.price, d.info, d.image, cd.id cuidine_dish_id"
						+ " from dish d, cuisine_dish cd "
						+ " where cd.dish_id = d.id "
						+ " and cd.id = ? ";
				Record record = Db.findFirst(sql, cuisineDishId);
				double sum = cart.get(String.valueOf(cuisineDishId)) * record.getNumber("price").doubleValue();
				total += sum;
			}
		}
		return total;
	}
}
  